package dao;

import model.Order;
import model.OrderItem;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.DBUtil;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class OrderDao {
    // 添加订单
    public void insertOrder(Connection con, Order order) throws SQLException {
        QueryRunner r = new QueryRunner();
        String sql = "insert into `order`(total, amount, status, paytype, name, phone, address, datetime, user_id) values(?,?,?,?,?,?,?,?,?)";
        r.update(con, sql, order.getTotal(), order.getAmount(), order.getStatus(),
                order.getPaytype(), order.getName(), order.getPhone(),
                order.getAddress(), order.getDatetime(), order.getUser().getId());
    }

    // 获取最后插入的id
    public int getLastInsertId(Connection con) throws SQLException {
        QueryRunner r = new QueryRunner();
        String sql = "select last_insert_id()";
        BigInteger bi = r.query(con, sql, new ScalarHandler<BigInteger>());
        return Integer.parseInt(bi.toString());
    }

    // 插入订单项
    public void insertOrderItem(Connection con, OrderItem item) throws SQLException {
        QueryRunner r = new QueryRunner();
        String sql = "insert into orderitem(price, amount, goods_id, order_id) values(?,?,?,?)";
        r.update(con, sql, item.getPrice(), item.getAmount(), item.getGoods().getId(), item.getOrder().getId());
    }

    // 查询用户的所有订单
    public List<Order> selectAll(int userid) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDataSource());
        String sql = "select * from `order` where user_id=? order by datetime desc";
        return r.query(sql, new BeanListHandler<Order>(Order.class), userid);
    }

    // 查询订单的所有订单项
    public List<OrderItem> selectAllItem(int orderid) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDataSource());
        String sql = "select i.id, i.price, i.amount, g.name as goodsName from orderitem i, goods g where order_id=? and i.goods_id=g.id";
        return r.query(sql, new BeanListHandler<OrderItem>(OrderItem.class), orderid);
    }

    // 获取订单数量
    public int getOrderCount(int status) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDataSource());
        String sql = "";
        if (status == 0) {
            sql = "select count(*) from `order`";
            return r.query(sql, new ScalarHandler<Long>()).intValue();
        } else {
            sql = "select count(*) from `order` where status=?";
            return r.query(sql, new ScalarHandler<Long>(), status).intValue();
        }
    }

    // 查询订单列表（分页）
    public List<Order> selectOrderList(int status, int pageNo, int pageSize) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDataSource());
        String sql = "SELECT o.id, o.total, o.amount, o.status, o.paytype, o.name, o.phone, o.address, o.datetime, u.username " +
                "FROM `order` o, user u " +
                "WHERE o.user_id = u.id ";
        if (status != 0) {
            sql += "AND o.status = ? ";
        }
        sql += "ORDER BY o.datetime DESC LIMIT ?, ?";

        System.out.println("Executing SQL: " + sql); // 打印SQL语句
        System.out.println("Status: " + status + ", PageNo: " + pageNo + ", PageSize: " + pageSize); // 打印参数

        if (status != 0) {
            return r.query(sql, new BeanListHandler<Order>(Order.class, new BasicRowProcessor(new GenerousBeanProcessor())), status, (pageNo - 1) * pageSize, pageSize);
        } else {
            return r.query(sql, new BeanListHandler<Order>(Order.class, new BasicRowProcessor(new GenerousBeanProcessor())), (pageNo - 1) * pageSize, pageSize);
        }
    }

    // 更新订单状态
    public void updateStatus(int id, int status) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDataSource());
        String sql = "update `order` set status=? where id = ?";
        r.update(sql, status, id);
    }

    // 删除订单
    public void deleteOrder(Connection con, int id) throws SQLException {
        QueryRunner r = new QueryRunner();
        String sql = "delete from `order` where id = ?";
        r.update(con, sql, id);
    }

    // 删除订单项
    public void deleteOrderItem(Connection con, int id) throws SQLException {
        QueryRunner r = new QueryRunner();
        String sql = "delete from orderitem where order_id=?";
        r.update(con, sql, id);
    }
}